﻿var Excel = require('exceljs');
var mongoose = require('mongoose');

mongoose.connect("mongodb://localhost:27017/mern-starter");
var Mark = require('./mark_model.js').grade;
var Student = require('../mongo_student.js').students;

var exam_id_list =["001","002","003"];//手动修改


var workbook = new Excel.stream.xlsx.WorkbookWriter({
  filename: './ALL_grades.xlsx'
});
var worksheet = workbook.addWorksheet('Sheet');

var headers =  [
  { header: 'id', key: 'id' },
  { header: 'name', key: 'name' },
  { header: 'class', key: 'class' }
];
for(let i in exam_id_list){
	headers.push( {header: "mark"+exam_id_list[i], key: "mark"+exam_id_list[i]} );
}

worksheet.columns = headers;
var all_grades = new Array(exam_id_list.length);//[   [ {id/name/class/mark}, ...], [], []     ]

//生成0,1数组判断是否异步全部执行完成
var judge_processed = new Array(exam_id_list.length);
judge_processed.fill(0);

var all_stu_ids_list = new Array();//存储所有学生id，以防某次成绩没有不显示该学生

for(let idx in exam_id_list){
	Mark.find({"exam_id":exam_id_list[idx]}, {grades:1, _id:0}, function(err,docs){
		if(docs.length > 0){
			var stuID_arr = new Array();//一次exam stuID array
			var stuID_grade_list = new Array();//一次exam成绩list
			for(let i in docs){
				var grade_group_str = docs[i].grades;
				var grade_group = grade_group_str.split('/');
		
				for(let j in grade_group){ 
					stuID_arr.push(grade_group[j].split(':')[0]);
			
					var grade_one = new Object();
					grade_one.id = grade_group[j].split(':')[0];
					grade_one.mark = grade_group[j].split(':')[1];
					stuID_grade_list.push(grade_one);
				}
			}
		
			//生成all_stu_ids_list
			for(let j in stuID_arr){
				if(all_stu_ids_list.indexOf(stuID_arr[j]) == -1){
					all_stu_ids_list.push(stuID_arr[j]);
				}
			}
		
			console.log("SS");
			console.log(all_stu_ids_list);
		
			//查询姓名和班级
			Student.find({"studentNo":{"$in":stuID_arr}}, {classNo:1, name:1, studentNo:1, _id:0}, function(err,docs){
				//console.log(docs);
				var grade_arr = new Array();//一次exam加上班级和姓名的成绩list
				
				if(docs.length > 0){
					for(let j in docs){
						for(let z in stuID_grade_list){
							if(stuID_grade_list[z].id == docs[j].studentNo){
								var grade_one = new Object();
								grade_one.id = stuID_grade_list[z].id;
								grade_one.mark = stuID_grade_list[z].mark;
								grade_one.name = docs[j].name;
								grade_one.class = docs[j].classNo;
								
								grade_arr.push(grade_one);
							}
						}
					}
					
					//console.log(grade_arr);
					//加到总表all_grades里
					all_grades[idx] = grade_arr;
					//这次exam查询执行完毕
					judge_processed[idx] = 1;
					var judge_length = 0;
					for(let index in judge_processed){
						judge_length += judge_processed[index];
					}
			
					//如果全部执行完毕转换excel
					if(judge_processed.length == judge_length){
						console.log("RR");
						console.log(all_grades);
				
						var excel_result = new Array();
				
						//循环统计每一个学生
						for(let i in all_stu_ids_list){
							var one_stu_marks = new Object();
							//先遍历所有成绩给该学生的id name class赋值
							for(let index1 in all_grades){				
								for(let j in all_grades[index1]){
									if(all_grades[index1][j].id == all_stu_ids_list[i]){
										one_stu_marks.id = all_grades[index1][j].id;
										one_stu_marks.name =all_grades[index1][j].name;
										one_stu_marks.class = all_grades[index1][j].class;
									}
								}
							}
						
							//给该学生的各次exam成绩赋值（没有成绩计为0）
							for(let index1 in all_grades){				
								var flag_temp = 0;
								for(let j in all_grades[index1]){					
									if(all_grades[index1][j].id == all_stu_ids_list[i]){
										var temp_header = "mark"+ exam_id_list[index1];
										one_stu_marks[temp_header] = all_grades[index1][j].mark;
								
										flag_temp = 1;
									}						
								}
							
								if(flag_temp == 0){			
									var temp_header = "mark"+ exam_id_list[index1];
									one_stu_marks[temp_header] = 0;								
								}
							}
							//push进最终结果
							excel_result.push(one_stu_marks);
				
				 			//console.log("BB");
				 			//console.log(excel_result);
						}
						console.log("CC");
						console.log(excel_result);
				
						for(let ecl in excel_result) {
  							worksheet.addRow(excel_result[ecl]).commit();
						}
  						console.log('添加数据完毕：');
						workbook.commit();
					}
				}			
			})
		}
	});
}

/*
//查询数据库
Mark.find({"exam_id":exam_id}, {grades:1, _id:0}, function(err,docs){
	var stuID_arr = new Array();
	var stuID_grade_list = new Array();
	for(let i in docs){
		var grade_group_str = docs[i].grades;
		var grade_group = grade_group_str.split('/');
		
		for(let j in grade_group){ 
			stuID_arr.push(grade_group[j].split(':')[0]);
			
			var grade_one = new Object();
			grade_one.id = grade_group[j].split(':')[0];
			grade_one.mark = grade_group[j].split(':')[1];
			stuID_grade_list.push(grade_one);
		}
	}
		
	//查询姓名和班级
	Student.find({"studentNo":{"$in":stuID_arr}}, {classNo:1, name:1, studentNo:1, _id:0}, function(err,docs){
		//console.log(docs);
		var grade_arr = new Array();
				
		if(docs.length > 0){
			for(let j in docs){
				for(let z in stuID_grade_list){
					if(stuID_grade_list[z].id == docs[j].studentNo){
						var grade_one = new Object();
						grade_one.id = stuID_grade_list[z].id;
						grade_one.mark = stuID_grade_list[z].mark;
						grade_one.name = docs[j].name;
						grade_one.class = docs[j].classNo;
								
						grade_arr.push(grade_one);
					}
				}
			}
					
			//console.log(grade_arr);
			//to excel
			for(let i in grade_arr) {
  				worksheet.addRow(grade_arr[i]).commit();
  
			}
  			console.log('添加数据完毕：');
			workbook.commit();
		}
	});
})
	*/


/*
var data = [{
  id: 2014213111,
  name: '张三三',
  class: '2014215177',
  mark: '90'
  
},
{
  id: 2014213112,
  name: '李四四',
  class: '2014215178',
  mark: '66'
}
];
var length = data.length;

// 当前进度
var current_num = 0;
var time_monit = 400;
var temp_time = Date.now();

console.log('开始添加数据');
// 开始添加数据
for(let i in data) {
  worksheet.addRow(data[i]).commit();
  current_num = i;
  if(Date.now() - temp_time > time_monit) {
    temp_time = Date.now();
    console.log((current_num / length * 100).toFixed(2) + '%');
  }
}
console.log('添加数据完毕：', (Date.now() - start_time));
workbook.commit();

var end_time = new Date();
var duration = end_time - start_time;

console.log('用时：' + duration);
console.log("程序执行完毕");
*/
